{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c071866d-9519-41f0-bc89-1215c9f2ed97",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取excel的内容到 pd frame\n",
    "# pip install pandas openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "12962c59-4486-4af1-aa00-d3852193c82f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      工号   姓名 是否只读\n",
      "0  00191  胡剑英    是\n",
      "1  01038   史科    是\n",
      "2  01292   林勇    是\n",
      "3  04504  蒲光文    是\n",
      "4  06330  栗庆付    是\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import sys\n",
    "import time\n",
    "\n",
    "# 替换为你的 Excel 文件路径\n",
    "file_path = './input.xlsx'\n",
    "\n",
    "# 读取 Excel 文件的第一个工作表\n",
    "df = pd.read_excel(file_path)\n",
    "\n",
    "# 查看前几行\n",
    "print(df.head())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "35259381-1820-4731-b0d0-8e92e669a486",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "finish all works in 0.65399169921875 s---------\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "lines = []\n",
    "\n",
    "user_template =  \"INSERT INTO `t_user` (`region_arr`, `user_type`, `user_name`, `password`, `staff_name`, `job_number`, `lang_id`,  `is_using`, `insert_time` ) VALUES ('1', 1, '{name0}', '5e72a1c366d36f357ae24fb601ed7e848ad14f9f5117c9806e86de815ddbd689', '{name1}', '{name0}', 1,  1, now());\"\n",
    "role_tempLate = \"INSERT INTO `t_user_app` (`user_id`, `app_id`, `role_arr`, `insert_time`) select t.user_id, 4, '{role}', now() from t_user t where user_name = '{name}';\"\n",
    "for _, row in df.iterrows():\n",
    "    sql1 = user_template.format(name0=row['工号'], name1= row['工号'] if pd.isna(row['姓名']) else row['姓名'])\n",
    "    sql2 = role_tempLate.format(role= 21 if row['是否只读']=='是' else 19, name= row['工号'])\n",
    "    lines.append(sql1+\"\\n\")\n",
    "    lines.append(sql2+\"\\n\")\n",
    "        \n",
    "with open(\"output.txt\", \"a\", encoding=\"utf-8\") as f:\n",
    "    f.writelines(lines)\n",
    "    \n",
    "print(f\"finish all works in {(time.time()-start)} s---------\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f2c3c33-6367-4d39-abdf-e82b85c6f374",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
